let user = {
  login: `select JobNumber,Name,NserName,UserPassword,Permission,role,email,JobNumber,avatar FROM sys_user where NserName =?`,
  register: `insert sys_user (NserName,UserPassword,Permission,role_ids,role,Name,RegistrationTime) values (?,?,?,?,?,?,now())`,
  UpdateUserinfo: `update sys_user set Name=?,Permission=?,email=?,JobNumber=?   where NserName = ?`,
  updatepwd: `update sys_user set UserPassword=?  where NserName = ? and UserPassword =?`,
  getRoutes: `SELECT r.menu_id as menuId , r.path, r.name, r.component, r.requires_auth AS requiresAuth, r.menu_name as menuName, r.icon, r.parent_id  as parentId
  ,r.create_by as createBy,r.create_time as createTime ,r.update_by as updateBy ,r.update_time as updateTime ,r.remark ,r.order_num as orderNum ,r.query ,r.is_frame as  isFrame,r.is_cache as isCache ,r.menu_type as menuType ,r.visible ,r.status ,r.perms
  FROM sys_menu r  `,
  getUser: `select Name,NserName,Permission,email,JobNumber from sys_user where NserName = ?`,
  getMenu: `SELECT r.menu_id as menuId , r.path, r.name, r.component, r.requires_auth AS requiresAuth, r.menu_name as menuName, r.icon, r.parent_id as parentId,r.create_by as createBy,r.create_time as createTime ,r.update_by as updateBy ,r.update_time as updateTime ,r.remark ,r.order_num as orderNum ,r.query ,r.is_frame as  isFrame,r.is_cache as isCache ,r.menu_type as menuType ,r.visible ,r.status ,r.perms FROM sys_menu r where r.menu_id = ?`,
  // updateMenu: ` update	sys_menu set  path =?, name  =?, component  =?, requires_auth  =?, menu_name =?, icon =?, parent_id= ? ,  update_by  = ? , update_time  = now(), remark = ? , order_num  = ? , query = ? , is_frame = ? , is_cache  = ? , menu_type  = ? , visible = ? , status = ? , perms =? where menu_id = ? `,
  updateMenu: ` select count(0) as diff from sys_menu where path = ?  and menu_id <> ?;
UPDATE sys_menu AS sm1
JOIN (
    SELECT count(0)  AS dummy
    FROM sys_menu AS sm2
    WHERE sm2.path = ?
    AND sm2.menu_id <> ?
) AS temp ON 1 = 1
SET sm1.path = ?,sm1.name  =?, sm1.component  =?, sm1.requires_auth  =?, sm1.menu_name =?, sm1.icon =?, sm1.parent_id= ? ,  sm1.update_by  = ? , sm1.update_time  = now(), sm1.remark = ? , sm1.order_num  = ? , sm1.query = ? , sm1.is_frame = ? , sm1.is_cache  = ? ,sm1. menu_type  = ? , sm1.visible = ? , sm1.status = ? , sm1.perms =?
WHERE sm1.menu_id = ?
AND temp.dummy = 0;`,
  delMenu: `delete from sys_menu where menu_id = ? or parent_id = ?;delete from sys_role_menu where menu_id = ?;ALTER TABLE sys_menu AUTO_INCREMENT = 1;`,
  addMenu: `		insert into sys_menu ( path , name  , component  , requires_auth  , menu_name , icon , parent_id, remark , order_num  , query , is_frame , is_cache  , menu_type  , visible , status , perms  , create_by ,create_time) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now())`,
  // getAccount: `select JobNumber,Name,NserName,Permission,email,role,RegistrationTime,enable from sys_user where NserName like ? and  Name like ? and RegistrationTime like ? `,
  getAccount: `SELECT ur.id, ur.JobNumber,ur.Name,ur.NserName,ur.email,ur.role_ids as roleIds,
       GROUP_CONCAT(r.role_key ORDER BY r.role_id ASC SEPARATOR ',') AS Permission,
			 GROUP_CONCAT(r.role_name ORDER BY r.role_id ASC SEPARATOR ',') AS role,
			 ur.RegistrationTime,ur.enable
FROM sys_user ur
JOIN sys_role r ON FIND_IN_SET(r.role_id, ur.role_ids) > 0
GROUP BY ur.id;`,
  getOperationPermissions: `	SELECT r.perms FROM sys_menu r  JOIN sys_role_menu rp ON r.menu_id = rp.menu_id JOIN sys_role ro ON rp.role_id = ro.role_id WHERE  ro.status = 0 and ro.role_key in (?) and r.perms is not null and r.perms != '' GROUP BY r.menu_id `,
  // getRole: `	SELECT ro.role_key, GROUP_CONCAT(r.name SEPARATOR ', ') AS routeName,  GROUP_CONCAT(r.menu_name SEPARATOR ', ') AS titles FROM routes r JOIN role_route_permissions rp ON r.menu_id = rp.menu_id JOIN sys_role ro ON rp.role_id = ro.role_id GROUP BY ro.role_key  `,
  EditAccount: `update sys_user set JobNumber=?,Name=?,email=?,enable=?,role_ids=? where NserName=?`,
  //更新用户的角色key和name
  updateUserPermission: `UPDATE sys_user ur SET 
    ur.Permission = (
        SELECT GROUP_CONCAT(r.role_key ORDER BY r.role_id ASC SEPARATOR ',')
        FROM sys_role r
        WHERE FIND_IN_SET(r.role_id, ur.role_ids) > 0
    ),
    ur.role = (
        SELECT GROUP_CONCAT(r.role_name ORDER BY r.role_id ASC SEPARATOR ',')
        FROM sys_role r
        WHERE FIND_IN_SET(r.role_id, ur.role_ids) > 0
    );`,
  DeleteAccount: `delete from sys_user where NserName=?`,
  getAllRoute: `select * from routes`,
  listRole: `select distinct role_id as roleId, role_name as roleName, role_key as roleKey, role_sort as roleSort, data_scope as dataScope, menu_check_strictly as menuCheckStrictly, dept_check_strictly as deptCheckStrictly, status, del_flag as delFlag, create_time as createTime, remark from sys_role where del_flag = 0 `,
  roleMenuTreeselect: `select m.menu_id from sys_menu m left join sys_role_menu rm on m.menu_id = rm.menu_id where rm.role_id = ? and m.menu_id not in (select m.parent_id from sys_menu m inner join sys_role_menu rm on m.menu_id = rm.menu_id and rm.role_id = ?) order by m.parent_id, m.order_num;select menu_id, menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_time from sys_menu;`,
  updateRole: `delete from sys_role_menu where role_id=?;`,
  checkRoleUnique: `select * from sys_role where (role_name = ? or role_key = ?) and del_flag = 0`,
  addRoleName: `insert sys_role (role_name,role_key,role_sort,status,menu_check_strictly,dept_check_strictly,remark,create_by,update_by,create_time) values (?,?,?,?,?,?,?,?,?,now())`,
  delRole: `delete from sys_role_menu where role_id in (?);update sys_role set del_flag = 2,update_time = now(),update_by = ? where role_id in (?);ALTER TABLE sys_role AUTO_INCREMENT = 1;`,
  changeRoleStatus: `update sys_role set status = ?,update_by =?,update_time = now() where role_id = ?`,
  setLoginLog: `insert sys_loginlog (user_name,ipaddr,status,msg,access_time) values (?,?,?,?,now())`,
  getlogininfor: `select info_id as infoId,ipaddr,user_name as userName,status,msg,access_time as accessTime from sys_loginlog where 1=1 `,
  deleteLoginInfo: `delete from sys_loginlog `,
  setOperationLog: `insert sys_operlog (title,business_type,request_method,operator_type,oper_name,oper_url,oper_ip,oper_param,json_result,status,oper_time) values (?,?,?,?,?,?,?,?,?,?,now())`,
  deleteOperLog: `delete from sys_operlog `,
  getOperationLog: `select oper_id as operId,title,business_type as businessType,method,request_method as requestMethod,operator_type as operatorType,oper_name as operName,oper_url as operUrl,oper_ip as operIp,oper_param as operParam,json_result as jsonResult,status,error_msg as errorMsg,oper_time as operTime from sys_operlog where 1=1 `,
  updateAvatar: `update sys_user set avatar = ? where NserName = ?`,
};
let electric = {
  GetElectricAct: `SELECT eh.e_id, eh.name, eh.value, eh.diff, eh.createTime
FROM electric_history eh
INNER JOIN (
    SELECT e_id, MAX(createTime) AS latestTime
    FROM electric_history
    WHERE DATE(createTime) = ?
    GROUP BY e_id
) latest_records
ON eh.e_id = latest_records.e_id AND eh.createTime = latest_records.latestTime`,
  Get10DaySum: `SELECT DATE(eh.createTime) AS date, SUM(eh.diff) AS sum
FROM electric_history eh
JOIN (
    SELECT e_id, DATE(createTime) AS date, MAX(createTime) AS maxTime
    FROM electric_history
    GROUP BY e_id, DATE(createTime)
) AS latest
ON eh.e_id = latest.e_id AND DATE(eh.createTime) = latest.date AND eh.createTime = latest.maxTime
where 1=1 `,
  GetMonthSum: `SELECT DATE_FORMAT(createTime, '%Y-%m') AS date1, SUM(diff) AS sum from electric_history where year(createTime) = ? group by date1 order by date1`,
  GetConfigPara: `select * from config where is_delete = 0`,
  GetHourSum: `select date,createTime,hour , sum(diff) as diff from electric_history_hour `,
  EditData: `insert into config (e_id,ip,equipment,name,position,area,number,byte,length,enable) values ? ON DUPLICATE KEY UPDATE ip =VALUES(ip),equipment =VALUES(equipment),name =VALUES(name),position =VALUES(position),area =VALUES(area),number =VALUES(number),byte =VALUES(byte),length =VALUES(length),enable =VALUES(enable) `,
  AddData: `insert into config (ip,equipment,name,position,area,number,byte,length,enable,updateTime) values ?`,
  DeleteData: `update config set is_delete = 1 where e_id = ?`
};



module.exports = {
  electric,
  user,
};